USE MASTER
GO
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'StudentManagementSystem')
DROP DATABASE StudentManagementSystem
GO
CREATE DATABASE StudentManagementSystem
GO
USE StudentManagementSystem
GO
CREATE TABLE UserInformaion
(
	UserId nvarchar(100) PRIMARY KEY,
	[Password] nvarchar(100) NOT NULL,
	Firstname nvarchar(100)NOT NULL,
	Lastname nvarchar(100)NOT NULL,
	DOB smalldatetime NOT NULL,
	[Address] nvarchar(100) NOT NULL,
	Phone nvarchar(100) NOT NULL,
	Email nvarchar(100)NOT NULL
)
GO
CREATE TABLE [Role]
(
	RoleName nvarchar(100) PRIMARY KEY
)
GO
CREATE TABLE Semester
(
	SemName nvarchar(100) PRIMARY KEY
)
GO
CREATE TABLE CourceDetail
(
	CourceDetailId nvarchar(100) PRIMARY KEY,
	CourceName nvarchar(100) NOT NULL,
	SemName nvarchar(100) FOREIGN KEY REFERENCES Semester(SemName)
)
GO

CREATE TABLE [User]
(
	UserId nvarchar(100) PRIMARY KEY REFERENCES UserInformaion(UserId),
	RoleName nvarchar(100) FOREIGN KEY REFERENCES [Role](RoleName),	
	StatusLogin nvarchar(10) DEFAULT 'off' CHECK(StatusLogin = 'on' OR StatusLogin = 'off'),
	TimeLogin smalldatetime,
	TimeLogout smalldatetime
)
GO
CREATE TABLE Cource
(
	CourceDetailId nvarchar(100) FOREIGN KEY REFERENCES CourceDetail(CourceDetailId),
	UserId nvarchar(100) FOREIGN KEY REFERENCES [User](UserId)		
)
GO
CREATE TABLE [Subject]
(
	SubjectId nvarchar(100) PRIMARY KEY,
	SubjectName nvarchar(100) NOT NULL,
	TotalSession int NOT NULL
)
GO
CREATE TABLE Mark
(
	UserId nvarchar(100) FOREIGN KEY REFERENCES [User](UserId),
	SubjectId nvarchar(100)FOREIGN KEY REFERENCES [Subject](SubjectId),
	MarkW float,
	MarkP float,
	constraint MarkId PRIMARY KEY (UserId,SubjectId)
)
GO
CREATE TABLE ScheduleDetail
(
	ScheduleDetailId nvarchar(100) PRIMARY KEY,
	SubjectID nvarchar(100) FOREIGN KEY REFERENCES [Subject](SubjectId),
	TimeStart smalldatetime NOT NULL,
	TimeEnd smalldatetime NOT NULL,
	Room nvarchar(100) NOT NULL
)
GO
CREATE TABLE Schedule
(
	UserId nvarchar(100) FOREIGN KEY REFERENCES [User](UserId),
	ScheduleDetailId nvarchar(100) FOREIGN KEY REFERENCES  ScheduleDetail(ScheduleDetailId),
	constraint ScheduleId PRIMARY KEY(UserId, ScheduleDetailId) 	
)
GO
CREATE TABLE Attendance
(
	UserId nvarchar(100) FOREIGN KEY REFERENCES [User](UserId),
	ScheduleDetailId nvarchar(100) FOREIGN KEY REFERENCES  ScheduleDetail(ScheduleDetailId),
	[Status] nvarchar(10) NOT NULL CHECK([Status] = 'attend' OR [Status] = 'late' OR [Status] = 'absent'),
	TimeAttendace smalldatetime DEFAULT GETDATE()
	constraint AttendanceId PRIMARY KEY(UserId,ScheduleDetailId)
)
GO